3.1. Tabla sence_envios_programados

Esta tabla registrará cada intento de envío a SENCE, permitiendo su seguimiento y auditoría.

CREATE TABLE IF NOT EXISTS sence_envios_programados (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fecha_programada_envio DATE NOT NULL COMMENT 'Fecha para la cual se preparan los datos del reporte',
    codigo_envio_otec VARCHAR(255) NULL COMMENT 'ID interno opcional del OTEC para este envío (codigoEnvio SENCE)',
    rut_otec VARCHAR(12) NOT NULL COMMENT 'RUT del OTEC, ej: 76000111-K',
    id_sistema_sence INT NOT NULL DEFAULT 1350 COMMENT 'ID Fijo SENCE para LMS externas',
    token_sence_usado VARCHAR(255) NOT NULL COMMENT 'Token de autenticación SENCE utilizado',
    estado_actual_envio ENUM('pendiente', 'procesando_json', 'enviado_a_sence', 'exitoso_sence', 'error_otec', 'error_sence') NOT NULL DEFAULT 'pendiente',
    fecha_creacion_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_ultimo_intento_envio DATETIME NULL,
    sence_id_proceso_respuesta VARCHAR(255) NULL COMMENT 'ID del proceso devuelto por SENCE tras un envío exitoso',
    sence_log_respuesta TEXT NULL COMMENT 'Respuesta JSON completa de la API SENCE',
    INDEX idx_fecha_programada_envio (fecha_programada_envio),
    INDEX idx_estado_envio (estado_actual_envio)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3.2. Tabla sence_staging_cursos

Contendrá la información a nivel de curso para un lote de envío específico.

CREATE TABLE IF NOT EXISTS sence_staging_cursos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    envio_programado_id INT NOT NULL COMMENT 'FK a sence_envios_programados.id',
    id_curso_otec INT NOT NULL COMMENT 'FK al ID del curso en la tabla local cursos_sence',
    codigoOferta VARCHAR(255) NOT NULL COMMENT 'Código del curso SENCE (ej. registro_nacional)',
    codigoGrupo VARCHAR(255) NOT NULL COMMENT 'Código de la sección/grupo SENCE (a menudo igual a codigoOferta)',
    cantActividadSincronica_curso INT DEFAULT 0 COMMENT 'Número total de actividades sincrónicas del curso',
    cantActividadAsincronica_curso INT DEFAULT 0 COMMENT 'Número total de actividades asincrónicas del curso',
    fecha_actualizacion_staging TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (envio_programado_id) REFERENCES sence_envios_programados(id) ON DELETE CASCADE,
    FOREIGN KEY (id_curso_otec) REFERENCES cursos_sence(id) ON DELETE RESTRICT,
    UNIQUE KEY uq_envio_curso (envio_programado_id, id_curso_otec)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3.3. Tabla sence_staging_alumnos

Datos de cada alumno dentro de un curso, para un lote de envío.

CREATE TABLE IF NOT EXISTS sence_staging_alumnos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staging_curso_id INT NOT NULL COMMENT 'FK a sence_staging_cursos.id',
    id_alumno_otec INT NOT NULL COMMENT 'FK al ID del alumno en la tabla local alumnos',
    id_inscripcion_otec INT NOT NULL COMMENT 'FK al ID de la inscripción en la tabla local inscripciones_curso',
    rutAlumno VARCHAR(10) NOT NULL COMMENT 'RUT del alumno sin dígito verificador (parte numérica)',
    dvAlumno VARCHAR(1) NOT NULL COMMENT 'Dígito verificador del alumno',
    tiempoConectividad_total_curso INT DEFAULT 0 COMMENT 'Tiempo total de conectividad del alumno en el curso (segundos)',
    estado_sence_curso INT NOT NULL COMMENT 'Estado SENCE: 1=Cursando, 2=Aprobado, 3=Reprobado',
    porcentajeAvance_total_curso INT DEFAULT 0 COMMENT 'Porcentaje de avance general del alumno en el curso (0-100)',
    fechaInicio_curso_alumno DATE NOT NULL COMMENT 'Fecha de inicio del curso para el alumno (YYYY-MM-DD)',
    fechaFin_curso_alumno DATE NOT NULL COMMENT 'Fecha de término del curso para el alumno (YYYY-MM-DD)',
    fechaEjecucion_reporte DATE NOT NULL COMMENT 'Fecha a la que corresponde el avance reportado (YYYY-MM-DD)',
    evaluacionFinal_curso INT DEFAULT NULL COMMENT 'Nota final del alumno en el curso (0-100)',
    fecha_actualizacion_staging TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (staging_curso_id) REFERENCES sence_staging_cursos(id) ON DELETE CASCADE,
    FOREIGN KEY (id_alumno_otec) REFERENCES alumnos(id) ON DELETE RESTRICT,
    FOREIGN KEY (id_inscripcion_otec) REFERENCES inscripciones_curso(id) ON DELETE RESTRICT,
    UNIQUE KEY uq_stag_curso_alumno (staging_curso_id, id_alumno_otec)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3.4. Tabla sence_staging_modulos_alumno

Datos de cada módulo (interno: "tema") cursado por un alumno, para un lote de envío.

CREATE TABLE IF NOT EXISTS sence_staging_modulos_alumno (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staging_alumno_id INT NOT NULL COMMENT 'FK a sence_staging_alumnos.id',
    id_tema_otec INT NOT NULL COMMENT 'FK al ID del tema en la tabla local temas_curso',
    codigoModulo VARCHAR(255) NOT NULL COMMENT 'Código SENCE del módulo (proveniente de temas_curso)',
    tiempoConectividad_modulo INT DEFAULT 0 COMMENT 'Tiempo de conectividad del alumno en este módulo (segundos)',
    estado_sence_modulo INT NOT NULL COMMENT 'Estado SENCE del alumno en el módulo: 1=Cursando, 2=Aprobado, 3=Reprobado',
    porcentajeAvance_modulo INT DEFAULT 0 COMMENT 'Porcentaje de avance del alumno en este módulo (0-100)',
    fechaInicio_modulo_alumno DATE NULL COMMENT 'Fecha de inicio del módulo para el alumno (YYYY-MM-DD)',
    fechaFin_modulo_alumno DATE NULL COMMENT 'Fecha de término del módulo para el alumno (YYYY-MM-DD)',
    notaModulo INT DEFAULT NULL COMMENT 'Nota del alumno en el módulo (0-100)',
    cantActividadSincronica_modulo_def INT DEFAULT 0 COMMENT 'N° act. sincrónicas (definición del módulo)',
    cantActividadAsincronica_modulo_def INT DEFAULT 0 COMMENT 'N° act. asincrónicas (definición del módulo)',
    fecha_actualizacion_staging TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (staging_alumno_id) REFERENCES sence_staging_alumnos(id) ON DELETE CASCADE,
    FOREIGN KEY (id_tema_otec) REFERENCES temas_curso(id) ON DELETE RESTRICT,
    UNIQUE KEY uq_stag_alumno_modulo (staging_alumno_id, id_tema_otec)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3.5. Tabla sence_staging_actividades_alumno

Datos de cada actividad (interno: "subtema") dentro de un módulo, para un lote de envío. SENCE no pide muchos detalles aquí en el JSON, pero registrar su "código" es fundamental.

CREATE TABLE IF NOT EXISTS sence_staging_actividades_alumno (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staging_modulo_alumno_id INT NOT NULL COMMENT 'FK a sence_staging_modulos_alumno.id',
    id_subtema_otec INT NOT NULL COMMENT 'FK al ID del subtema en la tabla local subtemas_curso',
    codigoActividad VARCHAR(50) NOT NULL COMMENT 'Nombre o ID de la actividad SENCE (max 50 chars)',
    -- Se podría agregar un campo 'completada' (BOOLEAN) si ayuda a calcular el % de avance del módulo.
    fecha_actualizacion_staging TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (staging_modulo_alumno_id) REFERENCES sence_staging_modulos_alumno(id) ON DELETE CASCADE,
    FOREIGN KEY (id_subtema_otec) REFERENCES subtemas_curso(id) ON DELETE RESTRICT,
    UNIQUE KEY uq_stag_modulo_actividad (staging_modulo_alumno_id, id_subtema_otec)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3.6. Mapeo de Terminología Interna a SENCE

Para asegurar la correcta correspondencia de datos, se establece el siguiente mapeo conceptual:

• Concepto Interno OTEC: temas_curso (Tabla) → Concepto SENCE API: Módulo (Objeto JSON, campo codigoModulo)
• Concepto Interno OTEC: subtemas_curso (Tabla) → Concepto SENCE API: Actividad (Objeto JSON, campo codigoActividad)

4.1. Modificaciones en Scripts Existentes (Gestión de Alumnos e Inscripciones)

Estos scripts tendrán un rol menor, principalmente para datos identificatorios y estados iniciales:

• registrar_alumno_en_curso.php y inscribir_alumno.php:
  • Al crear una nueva inscripción, podrían generar un registro inicial en sence_staging_alumnos (asociado al lote 'pendiente' del día actual o próximo).
  • Poblarían campos como rutAlumno, dvAlumno, fechaInicio_curso_alumno, fechaFin_curso_alumno.
  • El campo estado_sence_curso se inicializaría en 1 ('Cursando').
  • El resto de campos de progreso (tiempoConectividad_total_curso, porcentajeAvance_total_curso, etc.) se inicializarían en 0 o NULL.
  • También podrían pre-poblarse los módulos (sence_staging_modulos_alumno) y actividades (sence_staging_actividades_alumno) asociados a ese alumno para ese curso, con valores de progreso por defecto.
• form_alumno.php:
  • Si se modifica el RUT de un alumno, se debería actualizar el rutAlumno y dvAlumno en los registros pendientes de sence_staging_alumnos para ese alumno.
• cambiar_estado_alumno.php y lógica de cambio de inscripciones_curso.modo:
  • Cambios en el estado del alumno (OTEC) o en el modo de la inscripción (ej. a 'aprobado', 'reprobado') deberían reflejarse en el campo estado_sence_curso y estado_sence_modulo en las tablas de staging, aplicando el mapeo a los códigos numéricos de SENCE. (Esto probablemente se maneje mejor en el script de consolidación).

4.2. Scripts del Aula Virtual y Seguimiento de Progreso (Lógica Nueva o Modificada)

Esta es la parte más dinámica. Los scripts que manejan la interacción del alumno con el contenido del curso deben actualizar las tablas de staging:

• Visualización de Contenido/Videos (aula_virtual_curso.php, actualizar_progreso_video.php):
  • Actualizarían tiempoConectividad_total_curso en sence_staging_alumnos.
  • Actualizarían tiempoConectividad_modulo en sence_staging_modulos_alumno.
• Finalización de Actividades/Subtemas (lógica de subtemas_alumno.modo = 'completado'):
  • Al completarse una actividad (subtema), se recalcularía y actualizaría porcentajeAvance_modulo en sence_staging_modulos_alumno.
  • Esto, a su vez, podría desencadenar una actualización del porcentajeAvance_total_curso en sence_staging_alumnos.
  • El codigoActividad en sence_staging_actividades_alumno se marcaría como completado (si se añade dicho campo).
• Registro de Notas y Evaluaciones:
  • Al ingresar/calcular notas para módulos (temas) o la evaluación final del curso, se actualizarían los campos notaModulo en sence_staging_modulos_alumno y evaluacionFinal_curso en sence_staging_alumnos.

4.3. Nuevo Script: Preparación Diaria y Consolidación de Lote (sence_preparar_lote_diario.php)

Este script es fundamental y se recomienda ejecutarlo mediante una tarea programada (CRON) diariamente, antes de la ventana de envío sugerida por SENCE (22:00-00:00 hrs).

Funciones Principales:

1. Crear un nuevo lote de envío:
   • Inserta un nuevo registro en sence_envios_programados para la fecha_programada_envio (generalmente, la fecha actual).
   • Estado inicial: 'pendiente'.
   • Genera un codigo_envio_otec único si se desea (ej. OTEC-YYYYMMDD-HHMMSS).
2. Iterar sobre Cursos y Alumnos Activos:
   • Selecciona todos los cursos de cursos_sence que estén activos y deban ser reportados.
   • Para cada curso, inserta/actualiza un registro en sence_staging_cursos asociado al envio_programado_id actual. Obtiene cantActividadSincronica_curso y cantActividadAsincronica_curso de la definición del curso.
   • Para cada alumno con inscripción activa (inscripciones_curso.estado = 'activo') en dicho curso:
     • Recopilar datos del alumno:
       • Extraer RUT y DV de alumnos.rut.
       • Obtener fechaInicio_curso_alumno y fechaFin_curso_alumno de inscripciones_curso.fecha_inicio_clases y inscripciones_curso.fecha_termino (o de cursos_sence si aplica a todos).
       • Mapear inscripciones_curso.modo al estado_sence_curso numérico (1: Cursando, 2: Aprobado, 3: Reprobado).
         • 'inscrito', 'preinscrito', 'confirmado', 'en_curso', 'cursando_online', 'pendiente_evaluacion', 'espera_practica' → 1 (Cursando)
         • 'aprobado', 'finalizado' (si implica aprobación), 'certificado_emitido' → 2 (Aprobado)
         • 'reprobado' → 3 (Reprobado)
         • *(Ajustar este mapeo según la lógica de negocio específica de la OTEC)*
       • Obtener evaluacionFinal_curso de inscripciones_curso.nota_final.
       • Calcular tiempoConectividad_total_curso: Podría ser una suma del tiempo en subtemas_alumno.tiempo_visualizacion para todas las actividades del alumno en ese curso, o de otra fuente de log de actividad. SENCE especifica un intervalo máximo de 45 minutos entre clics para considerar dedicación. Si se usa el plugin "Dedicación al Curso" de Moodle, se pueden tomar datos de ahí.
       • Calcular porcentajeAvance_total_curso: Basado en el número de actividades/subtemas obligatorios completados (subtemas_alumno.modo = 'completado') sobre el total de actividades obligatorias del curso.
       • Setear fechaEjecucion_reporte a la fecha actual.
       • Insertar/Actualizar el registro en sence_staging_alumnos.
     • Para cada Módulo (tema_curso) del curso:
       • Obtener codigoModulo (el código SENCE para ese `tema_curso`).
       • Obtener cantActividadSincronica_modulo_def y cantActividadAsincronica_modulo_def de la definición del `tema_curso`.
       • Calcular/Obtener tiempoConectividad_modulo para ese alumno en ese módulo.
       • Calcular/Obtener porcentajeAvance_modulo para ese alumno en ese módulo.
       • Obtener notaModulo (promedio de notas de actividades del módulo o una nota específica del módulo).
       • Determinar estado_sence_modulo (1,2,3) basado en el progreso y notas del alumno en el módulo.
       • Insertar/Actualizar el registro en sence_staging_modulos_alumno.
     • Para cada Actividad (subtema_curso) del módulo:
       • Obtener codigoActividad (título o ID del `subtemas_curso`, truncado a 50 caracteres).
       • Insertar/Actualizar el registro en sence_staging_actividades_alumno.
3. Actualizar estado del lote: Cambiar sence_envios_programados.estado_actual_envio a 'procesando_json' o un estado similar que indique que los datos están listos para ser convertidos a JSON y enviados.

5.1. Nuevo Script: Envío de Datos a la API SENCE (sence_enviar_api.php)

Este script puede ser llamado manualmente o también programado para ejecutarse después del script de consolidación.

Funciones Principales:

1. Seleccionar un lote pendiente: Busca un registro en sence_envios_programados con estado 'procesando_json' (o similar).
2. Construir el JSON:
   • Recupera los datos del lote desde sence_staging_cursos, sence_staging_alumnos, sence_staging_modulos_alumno, y sence_staging_actividades_alumno.
   • Ensambla la estructura JSON jerárquica exacta que SENCE requiere (ver sección siguiente).
3. Realizar la Petición a la API:
   • Envía el JSON mediante una petición POST al endpoint de SENCE.
   • Maneja timeouts (SENCE recomienda hasta 10 minutos para procesos largos).

     // Ejemplo conceptual en PHP con cURL para timeout
     // curl_setopt($ch, CURLOPT_TIMEOUT, 600); // 10 minutos
     // ini_set('max_execution_time', 600); // PHP script execution time

4. Procesar la Respuesta:
   • Guarda la respuesta completa de SENCE en sence_envios_programados.sence_log_respuesta.
   • Si el envío es exitoso, SENCE devuelve un id_proceso. Guárdalo en sence_envios_programados.sence_id_proceso_respuesta y actualiza el estado_actual_envio a 'exitoso_sence' o 'enviado_a_sence'.
   • Si hay errores, actualiza el estado_actual_envio a 'error_sence' o 'con_errores' y registra los detalles.

5.2. Estructura del JSON para la API SENCE

El JSON debe seguir la estructura detallada en el "Instructivo de Conexión: Integración entre LMS y SIC".

{
    "rutOtec": "76000111-K", // Ejemplo
    "idSistema": 1350,
    "token": "TOKEN-SENCE-OBTENIDO", // UUID en MAYÚSCULAS
    "codigoOferta": "CODIGO-CURSO-SENCE",
    "codigoGrupo": "CODIGO-SECCION-SENCE", // A menudo el mismo que codigoOferta
    "codigoEnvio": "ID-INTERNO-LOTE-OTEC-OPCIONAL",
    "cantActividadSincronica": 0, // Total actividades sincrónicas del CURSO
    "cantActividadAsincronica": 0, // Total actividades asincrónicas del CURSO
    "listaAlumnos": [
        {
            "rutAlumno": "11111111", // Sin DV
            "dvAlumno": "1",
            "tiempoConectividad": 3600, // Segundos, total del alumno en el CURSO
            "estado": 1, // 1:Cursando, 2:Aprobado, 3:Reprobado (en el CURSO)
            "porcentajeAvance": 50, // 0-100, avance general del alumno en el CURSO
            "fechaInicio": "2023-01-15", // Inicio del alumno en el CURSO
            "fechaFin": "2023-06-15", // Fin del alumno en el CURSO
            "fechaEjecucion": "2023-05-25", // Fecha del reporte
            "evaluacionFinal": 75, // 0-100, nota final del alumno en el CURSO
            "listaModulos": [
                {
                    "codigoModulo": "CODIGO-SENCE-MODULO-1",
                    "tiempoConectividad": 1800, // Segundos, del alumno en este MÓDULO
                    "estado": 1, // 1:Cursando, 2:Aprobado, 3:Reprobado (en este MÓDULO)
                    "porcentajeAvance": 70, // 0-100, avance del alumno en este MÓDULO
                    "fechaInicio": "2023-01-15", // Inicio del MÓDULO (puede ser igual al del curso)
                    "fechaFin": "2023-03-15", // Fin del MÓDULO
                    "notaModulo": 80, // 0-100, nota del alumno en este MÓDULO
                    "cantActividadSincronica": 0, // Total act. sincrónicas (definición del MÓDULO)
                    "cantActividadAsincronica": 0, // Total act. asincrónicas (definición del MÓDULO)
                    "listaActividades": [
                        { "codigoActividad": "Nombre o ID Actividad 1.1" }, // Máx 50 chars, se trunca
                        { "codigoActividad": "Nombre o ID Actividad 1.2" }
                    ]
                }
                // ... más módulos para este alumno
            ]
        }
        // ... más alumnos para este curso
    ]
}

Los campos y sus tipos de datos se encuentran en el instructivo de SENCE.

5.3. Endpoint y Autenticación de la API SENCE

• Endpoint para envío de avance: https://auladigital.sence.cl/gestor/API/avance-sic/enviarAvance (Método POST).
• Headers: Content-Type: application/json.
• Autenticación: Se realiza incluyendo los campos rutOtec, idSistema (valor fijo 1350 para LMS externas), y token directamente en el cuerpo del JSON.
  • El Token SENCE es un UUID que se obtiene desde https://sistemas.sence.cl/rts ingresando con el RUT del Organismo Ejecutor y el RUT del Representante Legal junto a su ClaveÚnica. Se debe generar un nuevo token y utilizar la cadena alfanumérica resultante.
  • Importante: El token debe ir en MAYÚSCULAS en el JSON.

5.4. Manejo de la Respuesta de la API SENCE

Si la información se envía correctamente, la API responderá con un JSON que incluye un id_proceso. Este ID es importante para seguimiento.

En caso de errores en los datos o en la estructura, la API devolverá un JSON con un campo datosError y/o un código de error específico.

Códigos de Error Comunes de SENCE (Resumen):

5.5. Prerrequisitos y Consideraciones SENCE

• Registro SENCE (RCE): Antes de poder reportar avance, los cursos, alumnos y relatores deben estar debidamente registrados y aprobados a través del sistema RCE de SENCE. La API de avance aquí descrita no maneja esos registros iniciales.
• Frecuencia de Envío: SENCE recomienda enviar la información de avance una vez al día, preferentemente entre las 22:00 y las 00:00 hrs (hora de Chile).
• Visualización en SIC: Los datos enviados y procesados correctamente por SENCE suelen estar visibles en la plataforma SIC al día hábil siguiente.
• Actualización de Datos: Cada envío de datos actualiza y sobrescribe la información anterior para el alumno en el curso y módulo respectivo. El porcentaje de avance (porcentajeAvance) enviado debe ser siempre mayor o igual al valor previamente enviado para el mismo alumno, módulo o curso.